Accesing MySQL from Django Application
Accessing database from Django application
To access database (here, I am considering MariaDB (MySQL database) version 10.5 or above using Django application.
We need a MySQL driver for Python. So, we have to install mysqlclient
pip install mysqlclient
We make change in the DATABASE section of settings.py file of the web application folder (here, settings.py inside of mysite folder. Initially, be default mysqlite database will be written in that file. We override with following code. You have to change database name, username and password.
settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'test',
'USER': 'root', #specify your database name, user name and password
'PASSWORD': 'root',
'HOST': 'localhost', # or your DB host
'PORT': '3306',
}
}
Retrieving data from database using Raw SQL
Here, I will use views.py file. In that file connenction module will be imported.
from django.db import connection
We have to import loader to render html templates in the views.py file , also have to import HttpResponse
A method will be defined to access mysql database as follows:
from django.template import loader
from django.http import HttpResponse
views.py
from django.shortcuts import render
from django.http import HttpResponse
from django.template import loader
from django.db import connection
def getData(request):
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM emp") #emp is table name
rows = cursor.fetchall()
fname = []
for r in rows:
fname.append(r)
dict = {"fname":fname}
template = loader.get_template('database.html')
return HttpResponse(template.render(dict))
Folder Structure myproject manage.py ...... mysite abc templates database.html views.py urls.py models.py apps.py .... mysite settings.py urls.py asgi.py wsgi.py .....
In the urls.py file of your web app (here, in example mysite), specify the route as follows:
path('getData/',views.getData,name='getData'),
Create html file in the templates folder as follows
database.html
Name of Employees
{% for r in fname %} {{r}} {%endfor%}